package dao.implementations.postgresql.Reporte;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import dao.implementations.DBBase;
import dao.interfaces.Reporte.IReporte;
import dao.transferObjects.Gae.GaeTO;
import dao.transferObjects.Gae.ReporteTO;

public class ReporteDB extends DBBase implements IReporte {
	
	public ReporteDB(Connection connection){
		this.conn = connection;
	}
	
	private static String CAMPOS_REPORTE = " id_reporte, id_gae, rut_gae, nombre_reporte, dia_creacion, mes_creacion, "+
										   " anio_creacion, fecha_creacion, descripcion, comentario_docente, " +
										   " numero_reporte ";
	
	public ReporteTO resultSet_a_ReporteTO(ResultSet rs)
	{
		ReporteTO reporte = new ReporteTO();
		
		try{
			reporte.setId_reporte(rs.getInt("id_reporte"));
			reporte.setId_gae(rs.getInt("id_gae"));
			reporte.setRut_gae(rs.getInt("rut_gae"));
			reporte.setNombre_reporte(rs.getString("nombre_reporte"));
			reporte.setDia_creacion(rs.getString("dia_creacion"));
			reporte.setMes_creacion(rs.getString("mes_creacion"));
			reporte.setAnio_creacion(rs.getString("anio_creacion"));
			reporte.setFecha_creacion(rs.getTimestamp("fecha_creacion"));
			reporte.setDescripcion(rs.getString("descripcion"));
			reporte.setComentario_docente(rs.getString("comentario_docente"));
			reporte.setNumero_reporte(rs.getInt("numero_reporte"));
		}
		catch (SQLException e){
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> resultSet_a_ReporteTO : " + e.getMessage());
		}
		
		return reporte;
	}
	
	@Override
	public int obtenerSgteIDreporte(){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int resp = -1;
		String query = "SELECT nextval('reporte_id_reporte_seq') as NEXTVAL";
		
		try{						
			pstmt = conn.prepareStatement(query);						
			rs = pstmt.executeQuery();
			
			if(rs.next())
				resp = rs.getInt("NEXTVAL");
			
			rs.close();			
		}
		catch (SQLException sqle) {
			System.out.print("Error en ReporteDB -> obtenerSgteIDreporte() : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}		
		return resp;
	}
	
	@Override
	public int obtenerSgteNumeroReporte(ReporteTO reporte){
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		int resp = -1;
		String query =  " SELECT count(*) as numero_reporte " +
						" FROM reporte " +
						" WHERE id_gae=? AND rut_gae=? ";
		
		try{						
			pstmt = conn.prepareStatement(query);						
			
			pstmt.setInt(1, reporte.getId_gae());
			pstmt.setInt(2, reporte.getRut_gae());
			
			rs = pstmt.executeQuery();
			
			if(rs.next())
				resp = rs.getInt("numero_reporte")+1;
			
			rs.close();			
		}
		catch (SQLException sqle) {
			System.out.print("Error en ReporteDB -> obtenerSgteNumeroReporte() : " + sqle.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}		
		return resp;
	}
	
	@Override
	public int reAsociarReporte(GaeTO gaeOriginal, GaeTO gaeNuevo){
		int result = -1;
		PreparedStatement pstmt = null;
		
		String query = " UPDATE reporte "+
					   " SET id_gae=?, rut_gae=? "+
					   " WHERE id_gae=? AND rut_gae=?";
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setInt(1, gaeNuevo.getId_gae());
			pstmt.setInt(2, gaeNuevo.getRut_gae());
			pstmt.setInt(3, gaeOriginal.getId_gae());
			pstmt.setInt(4, gaeOriginal.getRut_gae());
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase ReporteDB -> reAsociarReporte -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}

	@Override
	public int crearReporte(ReporteTO reporte) {
		PreparedStatement ps = null;
		int result = -1;

		String query =  " INSERT INTO reporte( " + CAMPOS_REPORTE + " ) "+
						" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); ";
		
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, reporte.getId_reporte());
			ps.setInt(2, reporte.getId_gae());
			ps.setInt(3, reporte.getRut_gae());
			ps.setString(4, reporte.getNombre_reporte());
			ps.setString(5, reporte.getDia_creacion());
			ps.setString(6, reporte.getMes_creacion());
			ps.setString(7, reporte.getAnio_creacion());
			ps.setTimestamp(8, reporte.getFecha_creacion());
			ps.setString(9, reporte.getDescripcion());
			ps.setString(10, reporte.getComentario_docente());
			ps.setInt(11, reporte.getNumero_reporte());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase ReporteDB -> crearReporte -> "+ ex.getMessage());
		}
		finally{
			if (ps != null) {
		          try {
		        	  ps.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}

	@Override
	public int modificarReporte(ReporteTO reporte) {
		return 0;
	}

	@Override
	public int eliminarReporte(ReporteTO reporte) {
		int result = -1;
		PreparedStatement pstmt = null;
		String query = " DELETE FROM reporte WHERE id_reporte=?;";
		
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setInt(1, reporte.getId_reporte());		
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase ReporteDB -> eliminarReporte -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		
		return result;
	}
	
	@Override
	public int eliminarTodosReportesGae(GaeTO gae) {
		int result = -1;
		PreparedStatement pstmt = null;
		
		String query = " DELETE FROM reporte "+
					   " WHERE id_gae=? AND rut_gae=?;";
		try{
			pstmt = this.conn.prepareStatement(query);
			
			pstmt.setInt(1, gae.getId_gae());
			pstmt.setInt(2, gae.getRut_gae());			
			
			result = pstmt.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase ReporteDB -> eliminarTodosReportesGae -> "+ ex.getMessage());
		}
		finally{
			if (pstmt != null) {
		          try {
		               pstmt.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	
	@Override
	public List<ReporteTO> obtenerReportesGae(GaeTO gae){
		List<ReporteTO> listaReportesGae = new ArrayList<ReporteTO>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String query = " SELECT "+ CAMPOS_REPORTE +
					   " FROM reporte WHERE id_gae = ? AND rut_gae = ? " +
					   " ORDER BY numero_reporte ";

		try{
			pstmt = conn.prepareStatement(query);
			pstmt.setInt(1, gae.getId_gae());
			pstmt.setInt(2, gae.getRut_gae());
			rs = pstmt.executeQuery();
			while(rs.next()){				
				listaReportesGae.add(this.resultSet_a_ReporteTO(rs));
			}
			rs.close();
			pstmt.close();
		}
		catch (SQLException sqle) {
			System.out.println("Error en "+this.getClass().getCanonicalName()+" -> obtenerReportesGae : " + sqle.getMessage());
		}
		if(listaReportesGae.size() == 0)
			return null;
		return listaReportesGae;
	}
	
	@Override
	public int actualizarNumeroReporte(ReporteTO reporte)
	{
		PreparedStatement ps = null;
		int result = -1;

		String query =  " UPDATE reporte " +
						" SET numero_reporte=? "+
						" WHERE id_reporte=?; ";
		
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setInt(1, reporte.getNumero_reporte());
			ps.setInt(2, reporte.getId_reporte());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase ReporteDB -> actualizarNumeroReporte -> "+ ex.getMessage());
		}
		finally{
			if (ps != null) {
		          try {
		        	  ps.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
	
	@Override
	public int comentarReporte(ReporteTO reporte)
	{
		PreparedStatement ps = null;
		int result = -1;

		String query =  " UPDATE reporte " +
						" SET comentario_docente=? "+
						" WHERE id_reporte=?; ";
		
		try{
			ps = this.conn.prepareStatement(query);
			
			ps.setString(1, reporte.getComentario_docente());
			ps.setInt(2, reporte.getId_reporte());
			
			result = ps.executeUpdate();
		}
		catch (SQLException ex){
			System.out.println("Error en la clase ReporteDB -> comentarReporte -> "+ ex.getMessage());
		}
		finally{
			if (ps != null) {
		          try {
		        	  ps.close();
		               
		          } catch (SQLException e) {
		               e.printStackTrace();
		          }
			}
		}
		return result;
	}
}
